﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using TrabalhoLAP.Model.Class;

namespace TrabalhoLAP.Model.DAO
{
    public class ProfessorDAO
    {
        public DataSet consultarProfessor(string nomeProfessor, string codProfessor, string loginProfessor, string statusRegistro)
        {
            AcessoBanco acess = new AcessoBanco();
            string sql;
            string sqlWhere = "";

            sql = "SELECT codUsuario, nomeUsuario, loginUsuario, MD5(senhaUsuario), tipoUsuario, statusRegistro FROM USUARIO ";

            if (!nomeProfessor.Equals(""))
                sqlWhere = nomeProfessor.Equals("") ? "" : "WHERE NOMEUSUARIO LIKE '%" + nomeProfessor + "%'";

            if (sqlWhere.Equals("") && !codProfessor.Equals(""))
                sqlWhere = codProfessor.Equals("") ? "" : "WHERE CODUSUARIO = " + codProfessor;
            else if ((!sqlWhere.Equals("")) && (!codProfessor.Equals("")))
                sqlWhere = codProfessor.Equals("") ? "" : " AND CODUSUARIO = " + codProfessor;

            if (sqlWhere.Equals("") && !loginProfessor.Equals(""))
                sqlWhere = codProfessor.Equals("") ? "" : "WHERE LOGINUSUARIO LIKE '%" + loginProfessor + "%'";
            else if ((!sqlWhere.Equals("")) && (!loginProfessor.Equals("")))
                sqlWhere = codProfessor.Equals("") ? "" : " AND LOGINUSUARIO LIKE '%" + loginProfessor + "%'";

            if (sqlWhere.Equals(""))
                sqlWhere = "WHERE STATUSREGISTRO = '" + statusRegistro + "'";
            else
                sqlWhere += " AND STATUSREGISTRO = '" + statusRegistro + "'";

            if(sqlWhere.Equals(""))
            {
                sqlWhere = "WHERE tipoUsuario = 'PR'";
            }
            else
            {
                sqlWhere += "AND tipoUsuario = 'PR'";
            }

            sql = sql + sqlWhere;

            return acess.executeSelectQuery(sql);
        }

        public bool cadastrarProfessor(string nomeProfessor, string loginProfessor, string senhaProfessor, string statusRegistro)
        {
            AcessoBanco acess = new AcessoBanco();
            string sql;

            sql = "INSERT USUARIO (nomeUsuario, loginUsuario, senhaUsuario, statusRegistro, tipoUsuario) VALUES('" + nomeProfessor + "','" + loginProfessor + "', MD5('" + senhaProfessor + "'), '";
            sql += statusRegistro + "','" + "PR')";
            return acess.executeQuery(sql);
        }

        public bool alterarProfessor(string codProfessor, string nomeProfessor, string loginProfessor, string senhaProfessor, string statusRegistro)
        {
            AcessoBanco acess = new AcessoBanco();
            string sql;

            sql = "UPDATE USUARIO SET CODUSUARIO = " + codProfessor + ", NOMEUSUARIO = '" + nomeProfessor + "', LOGINUSUARIO = '" + loginProfessor + "', SENHAUSUARIO = '" + senhaProfessor + "'";
            sql += ", STATUSREGISTRO = '" + statusRegistro + "' WHERE CODUSUARIO = " + codProfessor;

            return acess.executeQuery(sql);
        }
    }
}